/****** Object:  StoredProcedure [dbo].[GetMessagesOnForumPage]    Script Date: 10/13/2011 17:19:37 ******/
SET ANSI_NULLS OFF
GO

SET QUOTED_IDENTIFIER OFF
GO








/* ------------------------------------------------------------
    Called by list to get a specific page, ordered by last reply

    Test Data: { }
    Created: {10/31/2003 11:26:50 PM}
    Modified: {11/10/2003 10:35:49 PM} 
    Created by SPEditor 1.7.7
------------------------------------------------------------ */

CREATE PROCEDURE [dbo].[GetMessagesOnForumPage]
(
	 @forumID int,
	 @page int,
	 @recsPerPage int
)
AS

	SET NOCOUNT ON

	-- Find out the first and last record we want
	DECLARE @FirstRec int, @LastRec int, @ourlimit int    
	SET @FirstRec = (@Page - 1) * @RecsPerPage
	SET @LastRec = (@Page * @RecsPerPage + 1)      
	SET @ourlimit = @RecsPerPage * @Page

	--Create a temporary table  
	CREATE TABLE #MessagesOnForumPage
	(
		[Count] int IDENTITY,
		ParentMessageID int
	)
	   
	  
	SET ROWCOUNT @ourlimit

	INSERT INTO #MessagesOnForumPage (ParentMessageID)
	SELECT id
	FROM mestbl
	WITH (NOLOCK)
	WHERE forumid=@forumID AND thread_parent=0
	ORDER BY lastreply desc

	--SELECT * FROM #MessagesOnForumPage

	SET ROWCOUNT 0
	
	SELECT
		MesTbl.ID,
		MesTbl.ForumID,
		MesTbl.name as staticUsername,
		MesTbl.subject,
		MesTbl.mesdate,
		MesTbl.thread_parent,
		MesTbl.thread_id,
		MesTbl.locked,
		MesTbl.lastreply,
		MesTbl.threadviews,
		MesTbl.score,
		MesTbl.protectedscore,
		MesTbl.pollid,
		MesTbl.userID,
		Forum_Users.username,
		Forum_Groups.groupname
	FROM MesTbl
	LEFT JOIN Forum_Users ON MesTbl.userID = Forum_Users.userID
	LEFT JOIN Forum_Groups ON Forum_Users.userlevel = Forum_Groups.levelid
	WHERE 
		MesTbl.ID IN (SELECT ParentMessageID FROM #MessagesOnForumPage)
	OR
		MesTbl.Thread_ID IN (SELECT ParentMessageID FROM #MessagesOnForumPage)
	order by lastreply desc, mesdate desc

	DROP TABLE #MessagesOnForumPage
	






GO


